Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# User-Managed Flashback Features of Oracle

Oracle's flashback features, which let you undo damage to your database after logical data corruption, include the following:

- Oracle Flashback Database, which returns your entire database to a previous state without requiring you to restore files from backup;

- Oracle Flashback Table, which returns one or more tables to their contents at a previous time;

- Oracle Flashback Drop, which undoes the effects of the `DROP TABLE` operation;

- Oracle Flashback Query, which is used to query the contents of the database at a past time;

- Oracle Flashback Version Query, which lets you view past states of data;

- Oracle Flashback Transaction Query, which is used to review transactions affecting a table over time.

All of these operations are available within SQL*Plus, and none of them require the use of Recovery Manager. More details about using the flashback features of Oracle in data recovery situations are provided in Oracle Database Backup and Recovery Basics.

## Performing Flashback Database with SQL*Plus

The SQL*Plus `FLASHBACK DATABASE` command performs the same function as the RMAN `FLASHBACK DATABASE` command: it returns the database to a prior state.

Note that using Flashback Database requires that you create a flash recovery area for your database and enable the collection of flashback logs. See Oracle Database Backup and Recovery Basics for more details about how the Flashback Database feature works, requirements for using Flashback Database , and how to enable collection of flashback logs required for Flashback Database. The requirements and preparations are the same whether you use RMAN or user-managed backup and recovery.

To perform the `FLASHBACK DATABASE` operation in SQL*Plus:

1. Query the target database to determine the range of possible flashback SCNs. The following SQL*Plus queries show you the the latest and earliest SCN in the flashback window:

       SQL> SELECT CURRENT_SCN FROM V$DATABASE;

       SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
            FROM V$FLASHBACK_DATABASE_LOG;

2. Use other flashback features if necessary, to identify the SCN or time of the unwanted changes to your database.

3. Start SQL*Plus with administrator privileges, and run the `FLASHBACK DATABASE` statement to return the database to a prior `TIMESTAMP` or `SCN`. For example:

       FLASHBACK DATABASE TO SCN 46963;
       FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
       FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';
       FLASHBACK DATABASE
         TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

   Open the database read-only to examine the results of the Flashback Database operation. When the operation completes, you can open the database read-only and perform some queries to make sure you have recovered the data you need. If you find that you need to perform Flashback Database again to a different target time, then use `RECOVER DATABASE` to return the database back to the present time, and then try another `FLASHBACK DATABASE` statement.

   If you are satisfied with the results of Flashback Database, then you can re-open your database with the RESETLOGS option. If appropriate, you can also use an Oracle export utililty like Data Pump Export to save lost data, use `RECOVER DATABASE` to return the database to the present, and re-import the lost object.